Create Other Staging Table in Staging Database and Populate the Staging Tables 8

Continued





DFT-MarketToStateMapping - Drag and drop the data Flow task – double click –

1.     Drag and drop the Flat Source – Double click and add the Flat connection manager and select the sheet from the excel to load the data

2.     Drag and drop the Conditional Split - Double click and add condition to remove the Null values 

ISNULL(market) || ISNULL(USStateCode)

3.     Drag and drop the Derived Column transformation – Add column “Check_Numeric_Value

(DT_I4)market == (DT_I4)market ? 1 : 0

4.     Drag and drop the Derived Column transformation – replace Check_Numeric_Value

ISNULL(Check_Numeric_Market) ? 0 : 1

5.     Drag and drop the Conditional Split - Double click and add condition to remove the Null values 

Check_Numeric_Market== 1

6.     Drag and drop the Data Conversion and select Market and change the data type to Integer.

7.     Drag and drop the OLE DB Destination – double click – add OLE DB Destination manager – select the destination table and mapping on the column to the table.

 

 

DFT -QuarterlySupplyDemandModelNational - Drag and drop the data Flow task – double click –

1.     Drag and drop the Flat Source – Double click and add the Flat connection manager and select the sheet from the excel to load the data

2.     Drag and drop the Data Conversion and select all and convert them Unicode String(50)

3.     Drag and drop the Derived Column transformation – add Column ”QDates

 

TRIM(((SUBSTRING(Quarterly,1,2)
== "1Q" ? (SUBSTRING(Quarterly,3,2) + "0331") :
SUBSTRING(Quarterly,1,2) == "2Q" ? (SUBSTRING(Quarterly,3,2) +
"0630") : SUBSTRING(Quarterly,1,2) == "3Q" ?
(SUBSTRING(Quarterly,3,2) + "0930") : SUBSTRING(Quarterly,1,2) ==
"4Q" ? (SUBSTRING(Quarterly,3,2) + "1231") :
"200331"))) 

4.     Drag and drop the Derived Column transformation – Add column “QD

(DT_WSTR,8)((SUBSTRING(QDates,1,1) ==
"9" ? "19" + QDates : "20" + QDates))

5.     Drag and drop the Derived Column transformation – Add column “Dates

(SUBSTRING(QD,1,4) +
"-" + SUBSTRING(QD,5,2) + "-" + SUBSTRING(QD,7,2)) 

6.     Drag and drop the Data Conversion and select columns that to change the data type as in ref pic

 

7.     Drag and drop the OLE DB Destination – double click – add OLE DB Destination manager – select the destination table and mapping on the column to the table.

 




DFT-RegionSubRegionUSstates - Drag and drop the data Flow task – double click –

1.     Drag and drop the Flat Source – Double click and add the Flat connection manager and select the sheet from the excel to load the data

2.     Drag and drop the OLE DB Destination – double click – add OLE DB Destination manager – select the destination table and mapping on the column to the table.

 

DFT - SubMarketVsMarket By PropertyTypes- Drag and drop the data Flow task – double click –

1.     Drag and drop the Flat Source – Double click and add the Flat connection manager and select the sheet from the excel to load the data

2.     Drag and drop the Data Conversion and select columns that to change the data type as in ref pic

 

3.     Drag and drop the OLE DB Destination – double click – add OLE DB Destination manager – select the destination table and mapping on the column to the table

 

 

DFT - Moody Fips - Drag and drop the data Flow task – double click –

1.     Drag and drop the Flat Source – Double click and add the Flat connection manager and select the sheet from the excel to load the data

2.     Drag and drop the Conditional Split - Double click and add condition to remove the Null values 

 ISNULL(FIPS)
|| ISNULL([Geography:]) || ISNULL([GeoCode:])

3.     Drag and drop the Derived Column transformation – Add column “Check_Numeric_Value

                  (DT_I4)FIPS == (DT_I4)FIPS ? 1 : 0

4.     Drag and drop the Derived Column transformation – replace Check_Numeric_Value

                    ISNULL(CHECK_NUMERIC_FLAG) ? 0 : 1

5.     Drag and drop the Conditional Split - Double click and add condition to remove the Null values 

       Check_Numeric_Market== 1

6.     Drag and drop the Data Conversion and select FIPS and change the data type to Integer.

7.     Drag and drop the OLE DB Destination – double click – add OLE DB Destination manager – select the destination table and mapping on the column to the table.


CONTINUED on NEXT STEP

How to load Flat file source